0
582
VistasWhy the EXTRA is NULL in Mysql EXPLAIN? Why >= is Using index condition?
mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB
there is a table named t and it has two indexes named a and b. Insert into t 100000 rows data
mysql> create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call idata();
I do some experiments, some are as follows
Now, i want to know;
(1)why explain select * from t where a >= 90000; extra is Using index condition? it has index key, but it doesn't have index filter and table filter, so why it is Using index condition?
(2)why explain select * from t where a = 90000; extra is NULL? is needs to have an access to the table,if the first case is Using index condition, why the second can't be Using index condition?
(3)why explain select a from t where a >= 90000; extra is Using where; Using index? i know it uses cover index, so extra has Using index;but why extra has Using where? it means server needs to filter the data? but storage engine has already return the correct, why server needs to filer?
2 Respuestas
Responde la pregunta0
First, terminology...
"Using index" means that the (in this case) INDEX(a) contains all the columns needed. That is "the index is covering".
"Using index condition" is quite different. Internally, it is called ICP (Index Condition Pushdown). This refers to whether the "handler" checks the expression or whether the "condition" (a >= 90000) is handed off to the Engine (InnoDB) to do the work.
As for "Using where"; that is still a mystery to me, even after using MySQL for 20 years and looking thousands of Explains. I ignore it.
In all 3 of your cases, INDEX(a) is used. This is indicated primarily by "key" ("a"--the name of the key, not the column), "key_len" ("5": 4-byte INT plus 1 for NULLable), and secondarily by "type" (which does not say "All").
Further
If you change the 90000 to 70000, you may find that it will switch to a table scan. Why bounce back and forth between the Index's BTree and the data's BTree (via the
PRIMARY KEY). The Optimizer will assume that it will be faster to simply scan all the table, ignoring the rows that fail theWHEREclause.EXPLAIN FORMAT=JSON SELECT-- Gives you a lot more information. (Perhaps not much more info for this simple query.) One useful surprise is that it will show how many sorts the single mention of "filesort" really refers to. (A possibly easy way to make this happen isGROUP BY x ORDER BY y; that is group and order by different columns.)Explain rarely has such clean numbers, like your "10001". Usually, the "rows" columns is an approximation, sometimes a terrible approx.
The slowlog records "Rows examined"; it will probably say 10001 (or maybe only 10000) and 1 for your tests. For a table scan, it would be a full 100K.
Another way to get "Rows examined" is via the "Handler"
STATUSvalues. See http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts
0
Your first and last query make use of WHERE with implicit comparison to other rows, in that case it makes use of the index and shows it in the extra field (type range).
When you make a condition with 0-1 results, it can directly access them (O(1) lookup). No comparison or ordering happens, just take one row, return it.